import xlrd
import pymysql

con = pymysql.connect(host='localhost', user='root', password='123456', database='1_test', port=3306,
                      charset='utf8')
cursor = con.cursor()
book = xlrd.open_workbook(r'C:\Users\lenovo\Desktop\baidu-员工的人员信息.xls')
sheet = book.sheet_by_name('人员管理')
for r in range(1, sheet.nrows):
    data = [sheet.cell(r, col).value for col in range(sheet.ncols)]
    print(data)
try:
    with con.cursor() as cursor:
        for sheet in book:
            for r in range(1, sheet.nrows):
                sql = 'insert into user1(id,idcard,username,realname,pwd,telphone,email,age,sex,address,hiredate,sal,' \
                      'job,company) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
                a1 = sheet.cell(r, 0).value
                a2 = sheet.cell(r, 1).value
                a3 = sheet.cell(r, 2).value
                a4 = sheet.cell(r, 3).value
                a5 = sheet.cell(r, 4).value
                a6 = sheet.cell(r, 5).value
                a7 = sheet.cell(r, 6).value
                a8 = sheet.cell(r, 7).value
                a9 = sheet.cell(r, 8).value
                a10 = sheet.cell(r, 9).value
                a11 = sheet.cell(r, 10).value
                a12 = sheet.cell(r, 11).value
                a13 = sheet.cell(r, 12).value
                a14 = sheet.cell(r, 13).value
                values = (a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14)
                cursor.execute(sql, values)
        con.commit()
finally:
    cursor.close()
    con.close()
print("存储成功!")
